package com.chwn.curiousdiary.provider;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.chwn.curiousdiary.download.DownloadBean;
import com.chwn.curiousdiary.download.DownloadStatus;

import java.util.ArrayList;

/**
 * Created by Myshikin on 2017/4/27.
 * 凡是涉及到cursor.getXxx()的方法  cursor.getXxx(c.getColumnIndex("columName"))
 */
public class DownloadStoryStore {

    private StoryDB mStoryDB;

    private static DownloadStoryStore mInstance;

    private DownloadStoryStore(Context context) {
        mStoryDB = StoryDB.getInstance(context);
    }

    public static DownloadStoryStore getInstance(Context context) {
        if (mInstance == null) {
            synchronized (DownloadStoryStore.class) {
                if (mInstance == null) {
                    mInstance = new DownloadStoryStore(context);
                }
            }
        }
        return mInstance;
    }

    //          + DownloadStoryStoreColumns.DOWNSTATUS + " INT NOT NULL);");
    public void onCreate(final SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS " + DownloadStoryStoreColumns.NAME + " ("
                + DownloadStoryStoreColumns.ID + " TEXT NOT NULL PRIMARY KEY,"
                + DownloadStoryStoreColumns.TOOL_SIZE + " INT NOT NULL,"
                + DownloadStoryStoreColumns.FILE_LENGTH + " INT NOT NULL, "
                + DownloadStoryStoreColumns.URL + " TEXT NOT NULL,"
                + DownloadStoryStoreColumns.DIR + " TEXT NOT NULL,"
                + DownloadStoryStoreColumns.STORY_NAME + " TEXT NOT NULL,"
                + DownloadStoryStoreColumns.STORY_IMAGE + " TEXT NOT NULL,"
                + DownloadStoryStoreColumns.STROY_SHARE_URL + " TEXT NOT NULL,"
                + DownloadStoryStoreColumns.DESC + " TEXT NOT NULL,"
                + DownloadStoryStoreColumns.DOWNSTATUS + " INT NOT NULL, "
                + DownloadStoryStoreColumns.TYPE_ID + " TEXT ,"
                + DownloadStoryStoreColumns.STORY_LISTEN_COUNT + " TEXT ,"
                + DownloadStoryStoreColumns.STORY_CREATE_TIME + " TEXT  );");
    }

    public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
        if (oldVersion == 1) {
            try {
                db.beginTransaction();

                // 1, 重命名原表.
                String tempTableName = DownloadStoryStoreColumns.NAME + "_temp";
                String sql = "ALTER TABLE " + DownloadStoryStoreColumns.NAME + " RENAME TO " + tempTableName;
                db.execSQL(sql);

                onCreate(db);

                // 3, Load data
                sql = "INSERT INTO " + DownloadStoryStoreColumns.NAME +
                        " ("
                        + DownloadStoryStoreColumns.ID + " ,"
                        + DownloadStoryStoreColumns.TOOL_SIZE + ","
                        + DownloadStoryStoreColumns.FILE_LENGTH + " , "
                        + DownloadStoryStoreColumns.URL + ","
                        + DownloadStoryStoreColumns.DIR + " ,"
                        + DownloadStoryStoreColumns.STORY_NAME + ","
                        + DownloadStoryStoreColumns.STORY_IMAGE + ","
                        + DownloadStoryStoreColumns.STROY_SHARE_URL + ","
                        + DownloadStoryStoreColumns.DESC + ","
                        + DownloadStoryStoreColumns.DOWNSTATUS
                        + ") " +
                        " SELECT "
                        + DownloadStoryStoreColumns.ID + " ,"
                        + DownloadStoryStoreColumns.TOOL_SIZE + ","
                        + DownloadStoryStoreColumns.FILE_LENGTH + ", "
                        + DownloadStoryStoreColumns.URL + ","
                        + DownloadStoryStoreColumns.DIR + ","
                        + DownloadStoryStoreColumns.STORY_NAME + ","
                        + DownloadStoryStoreColumns.STORY_IMAGE + ","
                        + DownloadStoryStoreColumns.STROY_SHARE_URL + ","
                        + DownloadStoryStoreColumns.DESC + ","
                        + DownloadStoryStoreColumns.DOWNSTATUS
                        + " FROM " + tempTableName;

                db.execSQL(sql);

                // 4, Drop the temporary table.
                sql = "DROP TABLE IF EXISTS " + tempTableName;
                db.execSQL(sql);

                db.setTransactionSuccessful();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                db.endTransaction();
            }
        }
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DownloadStoryStoreColumns.NAME);
        onCreate(db);
    }

    /**
     * 增
     * @param entity
     */
    public synchronized void insert(DownloadBean entity) {
        Log.e("dataen", " id = " + entity.getStoryId());
        final SQLiteDatabase database = mStoryDB.getWritableDatabase();
        database.beginTransaction();
        try {
            ContentValues values = new ContentValues(10);
            values.put(DownloadStoryStoreColumns.ID, entity.getStoryId());
            values.put(DownloadStoryStoreColumns.TOOL_SIZE, entity.getTotalSize());
            values.put(DownloadStoryStoreColumns.FILE_LENGTH, entity.getCompletedSize());
            values.put(DownloadStoryStoreColumns.URL, entity.getUrl());
            values.put(DownloadStoryStoreColumns.DIR, entity.getSaveDirPath());
            values.put(DownloadStoryStoreColumns.STORY_NAME, entity.getStoryName());
            values.put(DownloadStoryStoreColumns.STORY_IMAGE, entity.getStoryImage());
            values.put(DownloadStoryStoreColumns.DESC, entity.getStoryDesc());
            values.put(DownloadStoryStoreColumns.STROY_SHARE_URL, entity.getStroyShareURL());
            values.put(DownloadStoryStoreColumns.DOWNSTATUS, entity.getDownloadStatus());
            values.put(DownloadStoryStoreColumns.STORY_CREATE_TIME, entity.getStoryCreateTime());
            values.put(DownloadStoryStoreColumns.STORY_LISTEN_COUNT, entity.getStoryListenCount());
            values.put(DownloadStoryStoreColumns.TYPE_ID, entity.getTypeId());
            database.replace(DownloadStoryStoreColumns.NAME, null, values);
            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
        }
    }

    /**
     * 改
     * @param entity
     */
    public synchronized void update(DownloadBean entity) {

        final SQLiteDatabase database = mStoryDB.getWritableDatabase();
        database.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            values.put(DownloadStoryStoreColumns.TOOL_SIZE, entity.getTotalSize());
            values.put(DownloadStoryStoreColumns.FILE_LENGTH, entity.getCompletedSize());
            values.put(DownloadStoryStoreColumns.URL, entity.getUrl());
            values.put(DownloadStoryStoreColumns.DIR, entity.getSaveDirPath());
            values.put(DownloadStoryStoreColumns.STORY_NAME, entity.getStoryName());
            values.put(DownloadStoryStoreColumns.STORY_IMAGE, entity.getStoryImage());
            values.put(DownloadStoryStoreColumns.DESC, entity.getStoryDesc());
            values.put(DownloadStoryStoreColumns.STROY_SHARE_URL, entity.getStroyShareURL());
            values.put(DownloadStoryStoreColumns.DOWNSTATUS, entity.getDownloadStatus());
            values.put(DownloadStoryStoreColumns.STORY_CREATE_TIME, entity.getStoryCreateTime());
            values.put(DownloadStoryStoreColumns.STORY_LISTEN_COUNT, entity.getStoryListenCount());
            values.put(DownloadStoryStoreColumns.TYPE_ID, entity.getTypeId());
            database.update(DownloadStoryStoreColumns.NAME, values, DownloadStoryStoreColumns.ID + " = ?",
                    new String[]{entity.getStoryId()});
            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
        }
    }

    /**
     * 删除单条
     * @param Id
     */
    public void deleteTask(String Id) {
        final SQLiteDatabase database = mStoryDB.getWritableDatabase();
        database.delete(DownloadStoryStoreColumns.NAME, DownloadStoryStoreColumns.ID + " = ?", new String[]
                {String.valueOf(Id)});
    }

    /**
     * 删除多条
     * @param Id
     */
    public void deleteTask(String[] Id) {
        final SQLiteDatabase database = mStoryDB.getWritableDatabase();
        database.delete(DownloadStoryStoreColumns.NAME, DownloadStoryStoreColumns.ID + " = ?", Id);
    }

    /**
     * 删除正在下载的任务
     */
    public void deleteDowningTasks() {
        ArrayList<String> results = new ArrayList<>();
        final SQLiteDatabase database = mStoryDB.getWritableDatabase();
        Cursor cursor = null;
        try {
            cursor = database.query(DownloadStoryStoreColumns.NAME, null,
                    null, null, null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                results.ensureCapacity(cursor.getCount());

                do {
                    if (cursor.getInt(cursor.getColumnIndex(DownloadStoryStoreColumns.DOWNSTATUS)) != DownloadStatus.DOWNLOAD_STATUS_COMPLETED)
                        results.add(cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.ID)));

                } while (cursor.moveToNext());
            }
            String[] t = new String[results.size()];
            for (int i = 0; i < results.size(); i++) {
                t[i] = results.get(i);
            }
            final StringBuilder selection = new StringBuilder();
            selection.append(DownloadStoryStoreColumns.ID + " IN (");
            for (int i = 0; i < t.length; i++) {
                selection.append(t[i]);
                if (i < t.length - 1) {
                    selection.append(",");
                }
            }
            selection.append(")");
            database.delete(DownloadStoryStoreColumns.NAME, selection.toString(), null);
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }

    /**
     * 删除全部
     */
    public synchronized void deleteAll() {
        final SQLiteDatabase database = mStoryDB.getWritableDatabase();
        database.delete(DownloadStoryStoreColumns.NAME, null, null);
    }

    /**
     * 根据Id查询故事
     * @param Id
     * @return
     */
    public synchronized DownloadBean getDownLoadedList(String Id) {
        Cursor cursor = null;
        DownloadBean entity = null;
        try {
            cursor = mStoryDB.getReadableDatabase().query(DownloadStoryStoreColumns.NAME, null,
                    DownloadStoryStoreColumns.ID + " = ?", new String[]{String.valueOf(Id)}, null, null, null);
            if (cursor == null) {
                return null;
            }

            if (cursor.moveToFirst()) {

                do {
                    entity = new DownloadBean(
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.ID)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_NAME)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_IMAGE)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.DESC)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.DIR)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STROY_SHARE_URL)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.URL)),
                            cursor.getInt(cursor.getColumnIndex(DownloadStoryStoreColumns.FILE_LENGTH)),
                            cursor.getLong(cursor.getColumnIndex(DownloadStoryStoreColumns.TOOL_SIZE)),
                            cursor.getLong(cursor.getColumnIndex(DownloadStoryStoreColumns.FILE_LENGTH)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_CREATE_TIME)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_LISTEN_COUNT)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.TYPE_ID))
                    );
                } while (cursor.moveToNext());
                return entity;
            } else
                return null;

        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }

    /**
     * 查询下载完成数据  --- 初始化DownloadBean 存在问题
     * @return
     */
    public synchronized ArrayList<DownloadBean> getDownLoadedListAllDowning() {
        ArrayList<DownloadBean> results = new ArrayList<>();

        Cursor cursor = null;
        try {
            cursor = mStoryDB.getReadableDatabase().query(DownloadStoryStoreColumns.NAME, null,
                    null, null, null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                results.ensureCapacity(cursor.getCount());

                do {
                    if (cursor.getInt(cursor.getColumnIndex(DownloadStoryStoreColumns.DOWNSTATUS)) != DownloadStatus.DOWNLOAD_STATUS_COMPLETED)
                        results.add(new DownloadBean(
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.ID)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_NAME)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_IMAGE)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.DESC)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.DIR)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STROY_SHARE_URL)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.URL)),
                                cursor.getInt(cursor.getColumnIndex(DownloadStoryStoreColumns.FILE_LENGTH)),
                                cursor.getLong(cursor.getColumnIndex(DownloadStoryStoreColumns.TOOL_SIZE)),
                                cursor.getLong(cursor.getColumnIndex(DownloadStoryStoreColumns.FILE_LENGTH)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_CREATE_TIME)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_LISTEN_COUNT)),
                                cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.TYPE_ID))
                        ));
                } while (cursor.moveToNext());
            }

            return results;
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }


    /**
     * 查询下载成功的故事id
     * @return
     */
    public synchronized String[] getDownLoadedListAllDowningIds() {
        ArrayList<String> results = new ArrayList<>();

        Cursor cursor = null;
        try {
            cursor = mStoryDB.getReadableDatabase().query(DownloadStoryStoreColumns.NAME, null,
                    null, null, null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                results.ensureCapacity(cursor.getCount());

                do {
                    if (cursor.getInt(cursor.getColumnIndex(DownloadStoryStoreColumns.DOWNSTATUS)) != DownloadStatus.DOWNLOAD_STATUS_COMPLETED)
                        results.add(cursor.getString(0));

                } while (cursor.moveToNext());
            }
            String[] t = new String[results.size()];
            for (int i = 0; i < results.size(); i++) {
                t[i] = results.get(i);
            }
            return t;
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }

    /**
     * 查询全部数据
     * @return
     */
    public synchronized ArrayList<DownloadBean> getDownLoadedListAll() {
        ArrayList<DownloadBean> results = new ArrayList<>();

        Cursor cursor = null;
        try {
            cursor = mStoryDB.getReadableDatabase().query(DownloadStoryStoreColumns.NAME, null,
                    null, null, null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                results.ensureCapacity(cursor.getCount());

                do {
                    results.add(new DownloadBean(
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.ID)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_NAME)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_IMAGE)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.DESC)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.DIR)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STROY_SHARE_URL)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.URL)),
                            cursor.getInt(cursor.getColumnIndex(DownloadStoryStoreColumns.FILE_LENGTH)),
                            cursor.getLong(cursor.getColumnIndex(DownloadStoryStoreColumns.TOOL_SIZE)),
                            cursor.getLong(cursor.getColumnIndex(DownloadStoryStoreColumns.FILE_LENGTH)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_CREATE_TIME)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.STORY_LISTEN_COUNT)),
                            cursor.getString(cursor.getColumnIndex(DownloadStoryStoreColumns.TYPE_ID))
                    ));
                } while (cursor.moveToNext());
            }

            return results;
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }

    /**
     * 查询下载个数
     * @return
     */
    public synchronized int getDownLoadedCount() {

        Cursor cursor = null;
        try {
            cursor = mStoryDB.getReadableDatabase().query(DownloadStoryStoreColumns.NAME, null,
                    null, null, null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                return cursor.getCount();
            }

            return 0;
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
    }

    interface DownloadStoryStoreColumns {
        /* Table name */
        String NAME = "downstory_info";

        String ID              = "id";
        String TOOL_SIZE       = "totalsize";
        String FILE_LENGTH     = "complete_length";
        String URL             = "url";
        String DIR             = "dir";
        String STORY_NAME      = "story_name";
        String DESC            = "desc";
        String STROY_SHARE_URL = "stroy_share_url";
        String STORY_IMAGE     = "story_image";
        String DOWNSTATUS      = "notification_type";

        String STORY_LISTEN_COUNT = "storyListenCount";

        String STORY_CREATE_TIME = "storyCreateTime";
        String TYPE_ID           = "typeId";


    }
}
